#! /usr/bin/env python
# -*- coding:utf-8 -*-
import logging
import pandas as pd
import numpy as np
import configparser
import os, sys
import model_condition as sc #TODO,4 将固定策略中的加载数据移至本脚本
import datetime
sys.path.append(sys.path[0])
import mysql_conn
config = configparser.ConfigParser(allow_no_value=True)
cur_path = os.getcwd()
logger = logging.getLogger()

"""
从数据库读取数据和参数信息
结构: 
    class Graph_load  #图谱参数获取
    class Device_load #设备及属性等信息获取
    class Data_load   #能量电量等数据获取
说明：非运行脚本
"""
__all__ = ['Graph_load','Device_load','Data_load']

class Graph_load:
    """
    获取图谱相关信息
    """
    def __init__(self, conn_file='mysql_conn.cnf'):
        self.graph_id = 0          #图谱ID
        self.graph_name = ''       #图谱名称
        self.graph_citycode = ''   #图谱城市编码
        self.strategy_type = 0     #策略类型
        self.work_starttime = ''   #工作开始时间
        self.work_endtime = ''     #工作结束时间
        self.graph_type = 2        #图谱类型
        self.hour_cache = None     #小时级天气缓存
        self.daly_cache = None     #天级天气缓存
        self.worktime_cache = None
        self.conn_file = conn_file #mysql 连接信息 文件名
    #使用graphid更新图谱参数
    def put_databyid(self, graphid):
        """
        使用graphid获取图谱属性信息
        更新类参数
        :param graphid: 图谱ID,int
        """
        db = mysql_conn.db_con() #获取连接信息
        if not db is None:
            sqlstr = "select graph_name,strategy_type,start_work_time,end_work_time,graph_type from graph_info where graph_id = %s"
            cursor = db.cursor()
            rownum = cursor.execute(sqlstr, graphid)
            rs = cursor.fetchall()
            if rownum > 0:
                self.graph_id = graphid       #图谱ID
                self.graph_name = rs[0][0]    #图谱名称
                self.strategy_type = rs[0][1] #策略类型
                self.work_starttime = rs[0][2]#工作开始时间
                self.work_endtime = rs[0][3]  #工作结束时间
                self.graph_type = rs[0][4]    #图谱类型
            else:
                logger.error("获取图谱失败！")
            sqlstr = "select city_code from station_info"
            rownum = cursor.execute(sqlstr)
            rs = cursor.fetchall()
            if rownum > 0:
                self.graph_citycode = rs[0][0]  #城市编码
            else:
                logger.error("获取城市码失败！")
            cursor.close()
            db.close()
        else:
            logger.error('无法连接数据库！')
        pass
    #使用日期获取图谱ID
    def put_databydate(self, datestr):
        """
        使用日期获取图谱ID
        :param datestr:  str,2020-01-01
        :return:graph_id:int,图谱id
        """
        db = mysql_conn.db_con()
        pd.set_option('display.max_columns', None)
        #显示所有行，把列显示设置成最大
        pd.set_option('display.max_rows', None)
        #取图谱信息,获取时间段
        sqlstr = "select graph_id,graph_start_time,graph_end_time from graph_info "
        graph = pd.read_sql(sqlstr, db)
        db.close()
        if graph.empty:
            logger.error('获取图谱ID失败')
        sdate = datetime.datetime.strptime(datestr, '%Y-%m-%d').date() if isinstance(datestr, str) else datestr.date()
        sdate_num = int(sdate.strftime('%m%d'))
        graph["num_sdate"] = graph["graph_start_time"].str.replace('-', '').astype('int64')
        graph["num_edate"] = graph["graph_end_time"].str.replace('-', '').astype('int64')
        graph_id = 0
        for index, row in graph.iterrows():
            # 处理跨年情况
            if row["num_sdate"] > row["num_edate"]:
                s1, s2, s3, s4 = row["num_sdate"], 1231, 101, row["num_edate"]
                if (sdate_num >= s1 and sdate_num <= s2) or (sdate_num >= s3 and sdate_num <= s4):
                    graph_id = row["graph_id"]
            else:
                s1, s2 = row["num_sdate"], row["num_edate"]
                if sdate_num >= s1 and sdate_num <= s2:
                    graph_id = row["graph_id"]
        self.put_databyid(graph_id)
        return graph_id
    #根据日期获取小时天气
    def load_weather_hour(self, datestr=None):
        """
        缓存明天天气数据
        :param datestr: str like 2021-12-08
        :return: dict {wind:[1,2,3], temp:[1,2,3], ...}
        """
        #查询缓存
        if self.hour_cache:
            return self.hour_cache
        tomorrow = Device_load().load_pre_date(datestr)
        #检查是否生成citycode信息
        if self.graph_citycode == '':
            self.put_databydate(tomorrow)
        #使用日期及citycode查询明天天气
        sql = "SELECT sdate,temperature,wind,humidity,weather FROM weather_forecast_hourly " \
              "WHERE sdate BETWEEN %s AND %s AND historycity_code=%s;"
        with mysql_conn.Session() as session:
            weather_info = session.query(sql, (tomorrow, tomorrow+datetime.timedelta(hours=23, minutes=59), self.graph_citycode))
            # 查询失败，使用历史表再次查询
            if not weather_info:
                sql = "SELECT sdate,temperature,wind,humidity,weather FROM weather_history_hourly " \
                      "WHERE sdate BETWEEN %s AND %s AND historycity_code=%s;"
                weather_info = session.query(sql, ( tomorrow, tomorrow + datetime.timedelta(hours=23, minutes=59), self.graph_citycode))
        # 转换结果格式为{'sdate':[...], 'wind':[...],...}
        weather_dict = {
            'sdate': [],
            'temperature': [],
            'wind': [],
            'humidity': [],
            'weather': []
        }
        if len(weather_info)>0:
            for i in weather_info:
                weather_dict['sdate'].append(i['sdate'])
                weather_dict['temperature'].append(i['temperature'])
                weather_dict['wind'].append(i['wind'])
                weather_dict['humidity'].append(i['humidity'])
                weather_dict['weather'].append(i['weather'])
            self.hour_cache = weather_dict
        else:
            logger.error('load weather_hour failed!')
        return weather_dict
    #根据日期获取日天气
    def load_weather_day(self, datestr=None):
        """
        缓存明天天气数据,天表
        :param datestr: str like 2021-08-22
        :return: dict {wind:1, temp:2, ...}
        """
        # 查询缓存
        if self.daly_cache:
            return self.daly_cache
        tomorrow = Device_load().load_pre_date(datestr)
        # 检查是否生成citycode信息
        if self.graph_citycode == '':
            self.put_databydate(tomorrow)
        #单次查询明天天气
        sql = "SELECT sdate,temphigh,templow,wind,humidity_high,humidity_low,weather FROM weather_forecast_daily WHERE sdate =%s AND historycity_code=%s"
        with mysql_conn.Session() as session:
            weather_info = session.query(sql, (tomorrow, self.graph_citycode))
        #转换结果格式为{'sdate':[...], 'wind':[...],...}
        try:
            weather_info[0]
        except IndexError:  # 如果查询结果为空，尝试从历史表再次查询
            sql = "SELECT sdate,temphigh,templow,wind,humidity_high,humidity_low,weather FROM weather_history_daily WHERE sdate =%s AND historycity_code=%s"
            with mysql_conn.Session() as session:
                weather_info = session.query(sql, (tomorrow, self.graph_citycode))
        try:
            self.daly_cache = weather_info[0]
        except IndexError:
            if self.hour_cache:
                weather_info = [self.hour_cache]
            else:
                raise ValueError('气象数据缓存失败,日期：', tomorrow)
        if len( weather_info)>0:
            return weather_info[0]
        else:
            logger.error('load weather_day failed!')
    #根据图谱获取是否需要提前放能标志
    def load_is_save_graph(self,graph_id):
        """
        根据图谱获取是否需要提前放能标志
        :param graph_id: int ,图谱id
        :return: is_save: 0或1,1表示需要提前放能,0表示不需要
        """
        db = mysql_conn.db_con()
        sqlstr = "select is_save from graph_info where graph_id=%s"
        is_save = pd.read_sql(sqlstr, db, params=[graph_id])["is_save"].values[0]  # 当前图谱下的设备id  dataframe columns = ['device_id']
        db.close()
        if is_save!=0:
            is_save = 1
        return int(is_save)
    #加载图谱工作时间
    def load_graph_worktime(self, graphid):
        """
        加载图谱工作时间
        :param graphid: int,图谱id
        :return: res：df,["start_work_time","end_work_time"] ,type is int
        """
        #检查缓存
        if self.worktime_cache:
            return self.worktime_cache
        sql = 'SELECT start_work_time,end_work_time FROM graph_info WHERE graph_id=%s'
        with mysql_conn.Session() as se:
            try:
                res = se.query(sql, graphid)[0]  # 如工程设定正确，结果中应仅有一条数据
            except IndexError:
                raise ValueError('TABLE station_info: Get data err, perhaps is empty')
        if not res:
            logging.error('load graph_worktime error !')
        res['start_work_time'] = datetime.datetime.strptime(res['start_work_time'], '%H:%M').hour
        res['end_work_time'] = datetime.datetime.strptime(res['end_work_time'], '%H:%M').hour
        self.worktime_cache = res
        return res
    #加载图谱类型
    def load_graph_type(self,datestr):
        db = mysql_conn.db_con()
        #取图谱信息,获取时间段
        sqlstr = "select graph_type,graph_start_time,graph_end_time from graph_info "
        graph = pd.read_sql(sqlstr, db)
        db.close()
        if graph.empty:
            logger.error('获取图谱类型失败')
        sdate = datetime.datetime.strptime(datestr, '%Y-%m-%d').date() if isinstance(datestr, str) else datestr.date()
        sdate_num = int(sdate.strftime('%m%d'))
        graph["num_sdate"] = graph["graph_start_time"].str.replace('-', '').astype('int64')
        graph["num_edate"] = graph["graph_end_time"].str.replace('-', '').astype('int64')
        graph_type = 2 #默认过渡季
        for index, row in graph.iterrows():
            # 处理跨年情况
            if row["num_sdate"] > row["num_edate"]:
                if sdate_num >= row["num_sdate"] or sdate_num < row["num_edate"]:
                    graph_type = row["graph_type"]
            else:
                if sdate_num >= row["num_sdate"] and sdate_num < row["num_edate"]:
                    graph_type = row["graph_type"]
        return graph_type
    #加载strategy_type策略类型
    def load_strategy_type(self,graph_id):
        """
        加载策略类型编码
        :param graph_id:图谱id,int
        :return: 策略类型编码,int,包括 1001:经济优先  1002:能源优先   1003:舒适优先
        """
        db = mysql_conn.db_con()
        sqlstr = "select strategy_type from graph_info where graph_id=%s"
        type_df = pd.read_sql(sqlstr, db, params=[int(graph_id)])["strategy_type"].values[0]  # 当前图谱下的策略类型
        if type_df ==None:
            type_df = 1001
        else:
            type_df = int(type_df)
        db.close()
        return type_df
class Device_load:
    """
    获取策略相关信息
    """
    def __init__(self, date=None,conn_file = 'mysql_conn.cnf'):
        self.conn_file = conn_file  #数据库链接信息存储文件
        self.station_info_cache = None
        self.date = date            #日期,datetime
        self.today = datetime.datetime.now()

    #定义预测日
    def load_pre_date(self, date):
        """
        根据当前时间获取预测日,datetime
        :param date: datetime
        :return: datetime
        """
        #定义日期默认值
        if date == None:
            date = self.today
        date = datetime.datetime.strptime(date, "%Y-%m-%d") if isinstance(date, str) else date

        #根据图谱下班时间,对预测日重新定义
        gp = Graph_load('mysql_conn.cnf')
        graph_id = gp.put_databydate(date)  # 获取target对应日期的图谱ID
        work_end = Graph_load().load_graph_worktime(graph_id)["end_work_time"]  # 图谱工作结束时间
        if date.hour >= work_end:  # 如果现在时间大于17，预测日是明日，否则是今日
            date_day = date + datetime.timedelta(days=1)
        else:
            date_day = date
        #将预测日转换为时分秒均为0的datetime格式
        date_day = datetime.datetime(date_day.year, date_day.month, date_day.day, 0, 0, 0)
        return date_day
    #预测日下的开始结束时间
    def load_pre_time(self, date_day, start_time, end_time):
        """
        获取策略开始时间,策略结束时间
        :param date_day:datatime,预测日
        :param start_time: int
        :param end_time: int
        :return:
            start_date ：list,[年，月，日，时，分，秒]
            end_date   ：list,[年，月，日，时，分，秒]
        """
        day_before = date_day - datetime.timedelta(days=1)
        if start_time < 0:
            start_time = 24 + start_time
        if end_time < 0:
            end_time = 24 + end_time
        gp = Graph_load('mysql_conn.cnf')
        graph_id = gp.put_databydate(date_day.strftime("%Y-%m-%d"))  # 获取target对应日期的图谱ID
        work_end = Graph_load().load_graph_worktime(graph_id)["end_work_time"]  # 图谱工作结束时间
        if start_time >= work_end:  #如果开始时间大于等于下班小时，则是昨天
            start_date = day_before+datetime.timedelta(hours=start_time)
        else:
            start_date = date_day+datetime.timedelta(hours=start_time)

        if end_time > work_end:  #如果结束时间大于下班小时，则是昨天
            end_date = day_before+datetime.timedelta(hours=end_time)
        else:
            end_date = date_day+datetime.timedelta(hours=end_time)
        return start_date, end_date
    #加载当前图谱下的设备ID
    def load_device_id(self,graph_id):
        """
        加载当前图谱下的设备id
        :param graph_id: int,图谱id
        :return: device_id:list,设备id,type is int
        """
        db = mysql_conn.db_con()
        sqlstr = "select device_id from graph_dev where graph_id=%s"
        device_id = pd.read_sql(sqlstr, db, params=[graph_id])["device_id"].tolist()
        db.close()
        if len(device_id)>0:
            return device_id
        else:
            logging.error("load device_id error")
    #加载当前图谱下的设备的各种属性
    def load_device(self,graph_id):
        """
        获取当前图谱下的设备的各种属性
        :param graph_id: int,图谱id
        :return:  dataframe,columns = [设备名称,设备ID,设备上限,设备功率,设备类型,是否纳入决策,工作时间,能效比,dev_code,固定/模型预测开关]
                            type = [str,int,int,int,int,int,str,int,str,int]
                            example = [水蓄能（冬）,20,2750,700,2,0,23-23,50,YC602,1]
        """
        db = mysql_conn.db_con()
        device_id = self.load_device_id(graph_id)  # 当前图谱的设备ID
        sqlstr = "select  device_name,device_id,cap,power,type_id,is_predicted,work_time,conversion_rate,dev_code," \
                 "start_button,end_button,end_condition,end_operator,end_value,interval_time " \
                 " from device where device_id in %s"
        device = pd.read_sql(sqlstr, db, params=[device_id])  # dataframe 当前图谱设备的属性信息
        db.close()
        if len(device)>0:
            return device
        else:
            logging.error("load device error!")
    #加载图谱下device_id开启信息
    def load_device_id_closed(self,graph_id):
        """
        过滤deviceid中条件关闭设备，仅保留启用设备
        :param graph_id: int,图谱id
        :return: re_device_id:list,开启设备的id,type is int
        """
        device_id = self.load_device_id(graph_id)
        graph = Graph_load()
        graph.put_databyid(graph_id)
        sql = 'SELECT device_id,end_condition,end_operator,end_value,end_button,device_monitor_switch ' \
              'FROM device WHERE device_id IN (%s' + ',%s' * (len(device_id) - 1) + ')'
        # TODO check是否关闭
        with mysql_conn.Session() as se:
            device = se.query(sql, device_id)
        date_day = self.load_pre_date(self.date)
        calculator = sc.RegularCondition(None, graph_id, date_day)
        # 手动生成天气缓存
        calculator.get_weather_day(date_day)
        calculator.get_weather_hour(date_day)
        re_device_id = []
        for i in device:
            # 跳过末尾空值
            if not i:
                continue
            # 判断设备是否关闭,开启返回0（接口设计失败）
            if calculator.get_closetime([i, i, i['device_id']]):
                re_device_id.append(i['device_id'])
            # 判断设备是否手动关闭,不重复加入
            elif i['device_monitor_switch'] == 0:
                re_device_id.append(i['device_id'])
        return re_device_id
    #加载当前图谱下的工况
    def load_relation(self,graph_id):
        """
        获取当前图谱下的工况
        :param graph_id: int,图谱id
        :return:
            device_relation : 当前图谱下工况表,df columns = [device_from,device_to,loss_rate,rel_name]
                                        type = [str,int,int,int,int,int,str,int,str]
        """
        db = mysql_conn.db_con()
        sqlstr = "select  device_from,device_to,loss_rate,rel_name  from relationship where graph_id=%s"
        device_relation = pd.read_sql(sqlstr, db, params=[graph_id])
        db.close()
        if len(device_relation) ==0:
            logging.error("load relation error!")
        # TODO 3,relation结果过滤条件关闭设备
        ban_device = self.load_device_id_closed(graph_id)
        # 删除包括关闭设备的工况
        device_relation = device_relation[~(device_relation['device_from'].isin(ban_device) | device_relation['device_to'].isin(ban_device))]
        if len(device_relation) ==0:
            logging.error("relation all closed error!")
        return device_relation
    #加载当前图谱下的可并行工况标记
    def load_relation_com(self,graph_id):
        """
        获取当前图谱下的可并行工况标记
        :param graph_id: int,图谱id
        :return:
            device_relation_com : df ,并行工况表 columns = [device_from,device_to,stgy_code]
                                             type = [str,int,int]
        """
        db = mysql_conn.db_con()
        sqlstr = "select  device_from,device_to,stgy_code,work_time  from relationship_com where graph_id=%s"
        relation_com = pd.read_sql(sqlstr, db, params=[graph_id])
        db.close()
        if len(relation_com) ==0:
            logging.error("load relation_com error!")
        return relation_com
    #加载当前图谱下的各时段电价
    def load_electricity(self,graph_id):
        """
        获取当前图谱下的各时段电价
        :param graph_id: int,图谱id
        :return: electricity :df, 各时段电价表 columns = [start_time,end_time,price]
                                        type = [str,str,loat]
        """
        db = mysql_conn.db_con()
        sqlstr = "select  start_time,end_time,price from electricity where graph_id=%s"
        electricity = pd.read_sql(sqlstr, db, params=[graph_id])
        db.close()
        if len(electricity)==0:
            logging.error("load electricity error!")
        electricity["sort"] = [int(i.split(":")[0]) for i in electricity["end_time"].values]
        electricity.sort_values(by="sort", ascending=True,inplace=True)
        return electricity
    #加载battery_code、energy_code
    def load_station_info(self):
        """
        查询station_info表，battery_code、energy_code字段，将结果转为大写返回
        :return:res:df,columns = ["battery_code","energy_code"],type is str
        """
        # 检查缓存
        if self.station_info_cache:
            return self.station_info_cache
        sql = 'SELECT id,battery_code,energy_code FROM station_info'
        with mysql_conn.Session() as se:
            try:
                res = se.query(sql)[0]  # 如工程设定正确，表中应仅有一条数据
            except IndexError:
                raise ValueError('TABLE station_info: Get data err, perhaps is empty')

        if not res:
            logging.error("load station_info error!")
            raise ValueError('TABLE station_info: Get data err, perhaps is empty')
        # 不需大小写及转换库中查出即为正确point
        # res['battery_code'] = res['battery_code'].split('.')[1] if '.' in res['battery_code'] else res['battery_code']
        # res['energy_code'] = res['energy_code'].split('.')[1] if '.' in res['energy_code'] else res['energy_code']
        self.station_info_cache = res
        return res
    #加载能量电量默认启用的model_id
    def load_model_id(self):
        """
        获取默认的model id
        :return:
            model_ene_id, int,能量模型的id
            model_ele_id, int,电量模型的id
        """
        db = mysql_conn.db_con()
        dev_code_ene,dev_code_ele = self.load_station_info()["energy_code"],self.load_station_info()["battery_code"]
        sql_ene = "select id from model where device_code= %s and is_used =1 "
        sql_ele = "select id from model where device_code= %s and is_used =1 "
        model_ene = pd.read_sql(sql_ene, db, params=[dev_code_ene])  # 原始数据
        model_ele = pd.read_sql(sql_ele, db, params=[dev_code_ele])  # 原始数据
        if len(model_ene)==0:
            logging.error("load ene model_id error!")
        elif len(model_ene)>1:
            logging.error("load ene model_id to many error!!")
        if len(model_ele)==0:
            logging.error("load ene model_ele error!")
        elif len(model_ene)>1:
            logging.error("load ene model_id to many error!!")
        model_ene_id = model_ene["id"].values[0]
        model_ele_id = model_ele["id"].values[0]
        db.close()
        return model_ene_id, model_ele_id

        # 获取能量、电量预测结果
    #加载model属性信息
    def load_model_info(self,model_id):
        """
        根据model_id,从model表获取模型参数
        :param model_id: int,模型id
        :return:model_info:df,columns = ["device_code","isjoin","join_path","model_path"]
                            type = [str,int,str,str]
        """
        db = mysql_conn.db_con()
        base_path = os.path.dirname(__file__)  # C:\Users\ld\Desktop
        sql = 'SELECT device_code,isjoin,join_path,model_path FROM model WHERE id=%s'
        model_info = pd.read_sql(sql, db, params=[model_id])  #原始数据
        if len(model_info)==0:
            logging.error("load model_info error!")
        model_info['model_path'] = base_path+os.sep+model_info['model_path'].values[0]  #模型相对路径
        return model_info
        # 从model表获取模型相关参数
    #加载升级工况
    def load_upgrade(self,date = None):
        """
        根据temp表create_time字段,距现在小于1小时的规则,获取工况升级策略
        :param date_time: datetime
        :return:
            df_data： 升级工况, columns = ["start_time","end_time","power","device_from","device_to","info"]
                                type = [int,int,float,int,int,str]
            status_mark：0,1布尔值，0表示没有读到升级信息,1表示有读到升级信息
        """
        # 1.从数据库读取升级策略
        db = mysql_conn.db_con()  # 获取连接信息
        time_now = self.today if date==None else date
        sql_data = "select start_time,end_time,power,device_from,device_to,info from strategy_temp where create_time>= %s and stgy_level =1"
        creat_time = (time_now - datetime.timedelta(hours=1)).strftime("%Y%m%d%H%M%S")  # 当前小时的前一个小时，获取工况升级的最早时间
        df_data = pd.read_sql(sql_data, db, params=[creat_time])
        db.close()
        # 2.将读取的df转换为字典
        status_mark = 1 if df_data.shape[0] > 0 else 0  # 判断库中有没有更新升级数据；没更新为0，更新为1
        logging.info(status_mark)
        if status_mark == 1:  # 如果更新了升级数据
            df_data["start_time"] = df_data["start_time"].dt.hour  #开始时间取小时
            df_data["end_time"] = [i  for i in df_data["end_time"].dt.hour.values]  # 结束时间取小时
        return df_data, status_mark
    #加载实际执行策略
    def load_actual(self,stgy_type,date = None):
        """
        获取output表中的真实执行策略
        :param date: datetime
        :return:
            df_data： 升级工况, columns = ["start_time","end_time","power","device_from","device_to","info"]
                                type = [int,int,float,int,int,str]        """
        db = mysql_conn.db_con()  # 获取连接信息
        gp = Graph_load()
        graph_id = gp.put_databydate(date)  # 获取target对应日期的图谱ID
        work_end = gp.load_graph_worktime(graph_id)["end_work_time"]  # 图谱工作结束时间
        start_time,end_time = self.load_pre_time(date, work_end, work_end)
        sql_data = "select start_time,end_time,power,device_from,device_to,info from strategy_output where start_time>= %s and end_time <%s and strategy_type =%s"
        df_data = pd.read_sql(sql_data, db, params=[start_time,end_time,stgy_type])  # 工况升级时间
        db.close()

        if df_data.empty:
            start_time = start_time-datetime.timedelta(days = 1)
            end_time = start_time-datetime.timedelta(days = 1)
            df_data = pd.read_sql(sql_data, db, params=[start_time,end_time,stgy_type])  # 工况升级时间
            db.close()
            logging.debug("load actual error ;and filled with date-1 !")

        if not df_data.empty:
            df_data["start_time"] = df_data["start_time"].dt.hour# 开始时间取小时
            end_minute = df_data["end_time"].dt.minute
            end_hour = df_data["end_time"].dt.hour
            end_hour = map(lambda x, y: x + 1 if y > 0 else x, end_hour, end_minute)
            df_data["end_time"] = list(end_hour)  # 结束时间取小时
            return df_data
        else:
            logging.error("The actual strategy is empty!")
            raise Exception("The actual strategy is empty!")  #异常被抛出
    #根据create_time开始结束时间加载temp表预测策略
    def load_temp(self, date, write_time_start, write_time_end,stgy_type):
        """
        获取temp表中的预测策略
        :param date: datetime
        :param write_time_start: datetime,create_time的开始时间
        :param write_time_end: datetime,create_time的结束时间
        :return:
            df_data： 升级工况, columns = ["start_time","end_time","power","device_from","device_to","info"]
                                type = [int,int,float,int,int,str]        """
        # 1.从数据库读取真实执行的策略
        db = mysql_conn.db_con()  # 获取连接信息
        gp = Graph_load()
        graph_id = gp.put_databydate(date)  # 获取target对应日期的图谱ID
        work_end = gp.load_graph_worktime(graph_id)["end_work_time"]  # 图谱工作结束时间
        start_time, end_time = self.load_pre_time(date, work_end, work_end)
        sql_data = "select start_time,end_time,power,device_from,device_to,info from strategy_temp where start_time>= %s and end_time <%s and create_time >=%s and create_time <=%s and stgy_type = %s"
        params = [start_time, end_time, write_time_start, write_time_end,stgy_type]
        df_data = pd.read_sql(sql_data, db, params=params)  # 工况升级时间
        db.close()

        if not df_data.empty:
            df_data["start_time"] = df_data["start_time"].dt.hour  # 开始时间取小时
            end_minute = list(df_data["end_time"].dt.minute)
            end_hour = list(df_data["end_time"].dt.hour)
            end_hour = map(lambda x, y: x + 1 if y > 0 else x, end_hour, end_minute)
            df_data["end_time"] = end_hour  # 结束时间取小时
            return df_data
        else:
            logging.error("The temp strategy is empty!")
            raise Exception("The temp strategy is empty!")  # 异常被抛出
class Data_load:
    """
    获取能量电量相关数据
    """
    def __init__(self, date=None):
        self.station_info_cache = None
        self.worktime_cache = None
        self.date = date  #target值对应的日期2011-11-11,datetime格式
        self.temp_code = "ajwldl.YC699"
    #加载日天气
    def load_day_weather(self, start_time, end_time):

        """
        根据开始时间和结束时间，获取天气数据
        :param start_time:str,形如 "20200101"
        :param end_time: str,形如 "20200101"
        :return:df,columns = [sdate ,day_wind,day_hum_max,day_hum_min,day_weather,day_t_max,day_t_min]
                    type = [str,int,int,int,int,int,int]
        """
        db = mysql_conn.db_con()
        #获取graph_citycode
        gp = Graph_load('mysql_conn.cnf')
        date_day = Device_load().load_pre_date(self.date)
        graph_id = gp.put_databydate(date_day)  # 获取target对应日期的图谱ID
        gp.put_databyid(graph_id)
        citycode = gp.graph_citycode
        # 读取数据
        if start_time >= datetime.datetime(datetime.datetime.today().year,datetime.datetime.today().month,datetime.datetime.today().day,0,0,0):
            sqlstr = "select sdate ,wind as day_wind,humidity_high as day_hum_max,humidity_low as day_hum_min,weather as day_weather, temphigh as day_t_max,templow as day_t_min from  weather_forecast_daily \
               where historycity_code = %s and sdate>= %s and sdate<=%s "
        else:
            sqlstr = "select sdate ,wind as day_wind, humidity_high as day_hum_max,humidity_low as day_hum_min,weather as day_weather ,temphigh as day_t_max,templow as day_t_min from  weather_history_daily \
                   where historycity_code = %s and sdate>= %s and sdate<=%s "
        df = pd.read_sql(sqlstr, db, params=[citycode, start_time.strftime("%Y%m%d"), end_time.strftime("%Y%m%d")])
        db.close()
        if len(df)==0:
            logging.error("load day_weather error !")
        return df

        # 获取原始数据
    #加载日真实值
    def load_data_day(self, start_time, end_time, dev_code):
        """
        读取并连接天气信息和原始信息，得到初始数据集
        :param start_time: str,形如，20200101
        :param end_time:  str,形如，20200101
        :param dev_code:  str，形如，YC1007
        :return:
            X：df,特征集
            y：df,标签集
            df_data：df,数据库读入的原始数据
        """
        #读入原始数据和天气数据
        db = mysql_conn.db_con()
        df_weather = self.load_day_weather(start_time, end_time)  # 天气数据
        if end_time >= datetime.datetime(datetime.datetime.today().year,datetime.datetime.today().month,datetime.datetime.today().day,0,0,0):  # 如果结束时间大于等于今天,按时间生成数据
            df_data = pd.DataFrame()
            df_data["sdate"] = [datetime.datetime.strftime(x, '%Y-%m-%d') for x in list(pd.date_range(start=start_time, end=end_time))]
            df_data["data"] = 1
            df_data.fillna(value=1, inplace=True)
        else:   #否则从数据库读取数据
            sql_data = "select sdate as sdate,data from data_day where sdate>= %s and sdate<=%s and dev_code =%s"
            df_data = pd.read_sql(sql_data, db, params=[start_time, end_time, dev_code])  # 原始数据
            if df_data.shape[0] == 0:
                df_data = pd.DataFrame()
                df_data["sdate"] = [datetime.datetime.strftime(x, '%Y-%m-%d') for x in list(pd.date_range(start=start_time, end=end_time))]
                df_data["data"] = 1
                df_data.fillna(value=1, inplace=True)
        #TODO 按天取
        if df_data.empty:
            logger.error('load data day error !')

        #天气数据与原始数据连接
        df_weather['sdate'] = pd.to_datetime(df_weather['sdate']).dt.date
        df_data['sdate'] = pd.to_datetime(df_data['sdate']).dt.date
        data = pd.merge(df_data, df_weather, how='left', on='sdate')
        data.fillna(value=1, inplace=True)
        #过滤异常值
        std = np.std(data["data"]) if np.std(data["data"]) > 0 else 1
        data["label_zscore"] = [abs(i - np.mean(data["data"])) / std for i in data["data"]]
        data = data.loc[(data["label_zscore"] <= 2.2)]
        data.drop("label_zscore", axis=1, inplace=True)
        db.close()
        # 划分数据X和标签Y
        X = data[[i for i in data.columns.tolist() if i not in ["data", "dev_code"]]]
        y = data["data"]
        return [X, y, df_data]

        # 获取小时级气象信息
    #加载小时天气
    def load_hour_weather(self, start_time, end_time, hour_list=None):
        """
        获取小时级气象信息
        :param historycity_code: 城市编码 str,形如，1789395
        :param start_time:       开始时间 str,形如，20200101
        :param end_time:         结束时间 str,形如，20200101
        :param hour_list:        小时数
        :return: dataframe
        """
        db = mysql_conn.db_con()
        # 获取graph_citycode
        gp = Graph_load('mysql_conn.cnf')
        date_day = Device_load().load_pre_date(self.date)
        graph_id = gp.put_databydate(date_day)  # 获取target对应日期的图谱ID
        gp.put_databyid(graph_id)
        citycode = gp.graph_citycode
        sqlstr = "select sdate,hour(sdate) dt_hour  ,temperature as hour_t,humidity as hour_hum,wind as hour_wind,\
       	    visibility as hour_vis,barometer as hour_bar,weather as hour_weather from  weather_history_hourly \
       	    where  historycity_code = %s and date(sdate)>= %s and date(sdate)<=%s "
        # 拼接小时数字符串，可以按小时筛选
        if hour_list is None:
            sqlstr = sqlstr % (citycode, start_time, end_time)
            df = pd.read_sql(sqlstr, db)
        else:
            sqlstr = sqlstr + " and  FIND_IN_SET (HOUR(sdate) ,%s) "
            df = pd.read_sql(sqlstr, db, params=(citycode, start_time, end_time, hour_list))
        if df.empty:
            logger.error('load hour_weather error!')
        df["sdate"] = pd.to_datetime(df["sdate"])
        df.set_index("sdate", inplace=True)
        # 按小时聚合数据
        df = df.resample('h').apply(lambda numlist: numlist.min() if numlist.min() <= 0 else numlist.max()).dropna()
        db.close()
        return df

        # 获取设备天数据
    #获取设备天数据从rt
    def load_devdata_day(self, dev_code, start_time, end_time):
        """
        获取设备天数据
        :param dev_code:str,设备code
        :param start_time:str,形如'20211101'
        :param end_time:str,形如'20211101'
        :return:df,columns = [sdate,data]
                    type = [str,float]
        """
        db = mysql_conn.db_con()
        pd.set_option('display.max_columns', None)
        # 显示所有行，把列显示设置成最大
        pd.set_option('display.max_rows', None)
        sqlstr = "select  date(date) as sdate,sum(data) as data   from data_rt \
       		where dev_code=%s and date(date)>= %s and date(date)<=%s group by date(date)"
        df = pd.read_sql(sqlstr, db, params=(dev_code, start_time, end_time))
        db.close()
        if df.empty:
            logger.error('load devdata_day error!')
        df["sdate"] = pd.to_datetime(df["sdate"])
        return df

        # 获取设备小时数据
    #获取设备小时数据从rt
    def load_devdata_hour(self, dev_code, start_time, end_time, hour_list='0'):
        """
        获取设备小时数据
        :param dev_code:str,设备code
        :param start_time:str,形如'20211101'
        :param end_time:str,形如'20211101'
        :param hour_list:
        :return:df,columns = [sdate,data, dt_hour]
                    type = [str,float,int]
        """
        db = mysql_conn.db_con()
        pd.set_option('display.max_columns', None)
        # 显示所有行，把列显示设置成最大
        pd.set_option('display.max_rows', None)
        sqlstr = "select  date(date) as sdate,data ,hour(date) as dt_hour from data_rt \
       		where dev_code=%s and date(date)>= %s and date(date)<=%s and  FIND_IN_SET (HOUR(date) ,%s)"
        df = pd.read_sql(sqlstr, db, params=(dev_code, start_time, end_time, hour_list))
        db.close()
        if df.empty:
            logger.error('load devdata_hour error!')
        df["sdate"] = pd.to_datetime(df["sdate"])
        return df
    #获取日预测数据
    def load_pre_day(self, dev_code, model_id):
        """
        获取 策略日能量和电量的预测值，模型编号和图谱ID
        :param dev_code:str,设备code
        :param model_id:int,模型id
        :return:
            daily:float,日预测需求
            model_id：int,模型版本
            graph_id：int,图谱id
        """
        db = mysql_conn.db_con()
        select_day = Device_load().load_pre_date(self.date).strftime("%Y%m%d")
        sqlstr = "select model_version,data,graph_id from pre_data_day where sdate = %s and device_code =%s and model_version = %s"
        select = pd.read_sql(sqlstr, db, params=[select_day, dev_code, model_id])  # 原始数据
        if len(select)==0:
            logging.error("load pre_day error!")
        daily = select["data"].values[0]  # data
        graph_id = select["graph_id"].values[0]  # graph_id
        db.close()
        return daily, model_id, graph_id
    #获取历年月最高温度或最低温度月均值df
    def load_month_mean(self,column_name):
        """
        根据model_id,从model表获取模型参数
        :param column_name: str,max或min，表示获取最高温度月均值或最低温度月均值
        :return:model_info:df,columns = ["month",column_name]
                                        type = [int,float]
        """
        db = mysql_conn.db_con()
        sql_month_mean = 'SELECT MONTH(sdate) as month,temphigh,templow FROM weather_history_daily'
        month_mean_df = pd.read_sql(sql_month_mean, db)  # 原始数据
        if len(month_mean_df)==0:
            logging.error("load  error!")
        df_group = month_mean_df.groupby(['month'], as_index=False).mean()  #按月求均值
        if len(df_group) <12:
            logging.error("lens of month_mean  <12 !")
        if column_name == "day_t_max":
            df_mean = df_group[["month", "temphigh"]]
        else:
            df_mean = df_group[["month", "templow"]]
        df_mean.columns = ["month", column_name[-3:]]
        return df_mean
    #####################################################################
    #加载室内温度
    def load_temp_indoor(self,date=None,temp_code=None):
        """
        加载当前预测日前一日上班时间内的室内温度均值
        :param date: 预测日
        :param temp_code: 室内温度编码
        :return: 当前预测日前一日的上班时间室内温度均值,float
        """
        db = mysql_conn.db_con()
        if temp_code ==None:
            temp_code = self.temp_code
        if date==None:
            date =  Device_load().load_pre_date(self.date)
        date = date-datetime.timedelta(days=1) #预测日的前一天
        gp = Graph_load()
        graph_id = gp.put_databydate(date.strftime("%Y-%m-%d"))  # 获取target对应日期的图谱ID        load_graph_worktime(self, graphid)
        work_start = gp.load_graph_worktime(graph_id)["start_work_time"]
        work_end = gp.load_graph_worktime(graph_id)["end_work_time"]
        start_time = date+datetime.timedelta(hours=work_start, minutes=0) #前一天上班开始时间
        end_time = date+datetime.timedelta(hours=work_end, minutes=0)  #前一天上班结束时间
        sql_data = "select data from data_rt where sdate>= %s and sdate<=%s and dev_code =%s"
        df_data = pd.read_sql(sql_data, db, params=[start_time, end_time, temp_code])  # 预测日前一天上班时段内各小时室内温度
        db.close()
        temp_indoor = df_data["data"].mean()-2 #减2是调整室内温度计偏差
        return temp_indoor
    #室内目标温度定义
    def load_temp_goal(self,dev_code,date=None,holiday_path = "/common/holiday.csv"):
        """
        根据图谱和节假日类型,获取室内温度标准值
        :param dev_code: 能量编码
        :param date: 预测日
        :param holiday_path:holiday文件所在的地址
        :return: list,[21,26],表示预测日前一日舒适室内温度的上下限
        """
        if date==None:
            date =  Device_load().load_pre_date(self.date)
        date = date-datetime.timedelta(days=1)
        gp = Graph_load()
        holiday = pd.read_csv(cur_path+holiday_path)
        holiday["SDATE"] = holiday["SDATE"].apply(str)
        holiday["SDATE"] = pd.to_datetime(holiday["SDATE"])
        graph_type = gp.load_graph_type(date.strftime("%Y-%m-%d"))            #图谱类型，3:冬季   1:夏季    2:过渡季
        holiday_type = holiday[holiday["SDATE"]==date]["IsHoliday"].values[0] #节假日类型, 0:工作日   1:周末   2:长假
        ini_path = cur_path + '/model/model_%s/model_%s.ini' % (dev_code, dev_code)  # 定制model.ini文件地址
        if not os.path.exists(ini_path):
            ini_path = cur_path + '/common/model.ini'  # 公用model.ini文件地址
        config.read(ini_path, "utf-8")
        dict_range = {} # 存放各种图谱类型和节假日类型组合情况下的适宜室内温度范围
        for i in config["temperature_goal"]:
            key_i = i.split(" ")[1].split(",")
            value_i = config["temperature_goal"][i].split(",")
            dict_range[(int(key_i[0]),int(key_i[1]))] = [int(j) for j in value_i]
        goal = dict_range.get((graph_type, holiday_type), [-50, 50]) #如果字典中没有定义,适宜室内温度范围为-50~50
        return goal,graph_type
        #####################################################################

if __name__ == '__main__':
    # print(Device_load().load_station_info())
    # sdate = datetime.datetime(2022,1,1)
    # temp_indoor = Data_load().load_temp_indoor(sdate)
    # print("temp_indoor:",temp_indoor)
    # Data_load().load_temp_goal("ajdlnt.point56",date=sdate, holiday_path="/common/holiday.csv")
    dev_code = Device_load().load_station_info()["energy_code"]
    print("dev_code:",dev_code)